import json
import pandas as pd
from jbar import bar
from sqlalchemy import create_engine, text
import os
import sys
sys.path.append(os.path.abspath(os.path.join(os.path.dirname(__file__), '..')))
# 读取 .env 文件
import dotenv
from config import create_db_engine, load_env_hsp_abbr
# 创建数据库引擎
engine = create_db_engine()
dotenv.load_dotenv()
# 导入规则扁平化处理模块
from common.rule_flatten_handle import flatten_json, unflatten_json

# 动态读取所有 HSPAGGR 环境变量
def get_hospital_list():
    """动态获取所有配置的医院简称"""
    hsp_list = []
    hspListRaw = load_env_hsp_abbr()
    for hspCode, hspAbbr in hspListRaw:
        hsp_list.append(hspAbbr)
    return hsp_list

# 创建医院列表
hsp_list = get_hospital_list()
print(f"发现 {len(hsp_list)} 个医院: {hsp_list}")

# 验证是否有配置的医院
if not hsp_list:
    print("错误: 没有在 .env 文件中找到任何医院配置 (HSPAGGR1, HSPAGGR2, ...)")
    print("请在 .env 文件中添加至少一个医院配置，例如:")
    print("HSPAGGR1=白沙")
    print("HSPAGGR2=市三")
    sys.exit(1)

# 验证对应的规则文件是否存在
missing_files = []
for hsp_abbr in hsp_list:
    rule_file = f'ruleData_apply_{hsp_abbr}.json'
    if not os.path.exists(rule_file):
        missing_files.append(rule_file)

if missing_files:
    print("错误: 以下规则文件不存在:")
    for file in missing_files:
        print(f"  - {file}")
    print("请确保为每个配置的医院都有相应的规则文件。")
    sys.exit(1)

# 导入各种子模块
from generate_pre import genIsValid, genDate, genHspLv, genInOrOut, genSelfpaid, genTrigger
from generate_item_usage import genItemUsage
from generate_dx import genDx
from generate_item import genItem
from generate_main import genAge, genHsp, genDayOld, genGender, genPeriod, genMedType
from generate_item_num import genItemNum
from generate_dept import genDept
from generate_item_num_ratio import genItemNumRatio
from generate_item_used_day import genItemUsedDay
from generate_item_time import genItemTime, genItemFirstTime
from generate_special import genA27, genA49, genA513, genA542, genA8, genB24, genE79, genI107, genI137, genI49, genM179, genM27, genC21
from generate_violate import genViolates
import textwrap

# 删除数据库中的 rule_apply 表中所有 sql 不为 null 的记录
with engine.connect() as conn:
    print("删除 rule_apply 表中所有 sql 不为 null 的记录...")
    conn.execute(text("DELETE FROM rule_apply WHERE sql IS NOT NULL"))
    conn.commit()
# 删除数据库中的 rule_apply 表中所有 key like '%sqlList%' 的记录
with engine.connect() as conn:
    print("删除 rule_apply 表中所有 key like '%sqlList%' 的记录...")
    conn.execute(text("DELETE FROM rule_apply WHERE key LIKE '%sqlList%'"))
    conn.commit()

# hsp_abbr = hsp_list[0] if hsp_list else None
for hsp_abbr in hsp_list:
    print(f"Processing hospital abbreviation: {hsp_abbr}")

    # # 读取ruleData_apply_{hsp_abbr}.json文件
    # with open(f'ruleData_apply_{hsp_abbr}.json', 'r', encoding='utf-8') as f:
    #     ruleData = json.load(f)

    # 从数据库 rule_apply 表中，读取对应hsp_abbr的规则数据
    with engine.connect() as conn:
        print(f"读入 rule_apply_{hsp_abbr}..")
        result = conn.execute(text(f"SELECT rule_id, key, type, value FROM rule_apply WHERE hsp_abbr = :hsp_abbr"), {'hsp_abbr': hsp_abbr})
        rows = result.fetchall()
        flatData = [{'rule_id': row[0], 'key': row[1], 'type': row[2], 'value': row[3]} for row in rows]
        ruleData = unflatten_json(flatData)
    print(f"一共读入 {len(ruleData)} 条规则.")

    # 遍历 ruleData 中的每个规则
    for ruleDatum in ruleData:
        # 获取规则的ruleId
        ruleId = ruleDatum.get('ruleId')

        # 如果是X开头的规则（线索规则），暂时跳过
        if ruleId.startswith('X'):
            continue

        # 获取规则的ruleActs
        ruleActs = ruleDatum.get('ruleActs', [])
        # 遍历 ruleAct
        for ruleAct in ruleActs:
            # 获取规则的 trigger
            trigger = ruleAct.get('trigger', {})
            # 获取规则的 filter_pre，这些是筛选前的一些前提条件
            filter_pre = ruleAct.get('filter_pre', {})
            # 获取规则的 ruleActId
            ruleActId = ruleAct.get('ruleActId')
            # 获取规则的 description，并且将里面的单引号去掉
            ruleActMsg = ruleAct.get('ruleAct', '')
            ruleActMsg = ruleActMsg if ruleActMsg else ''
            ruleActMsg = ruleActMsg.replace("'", "''")  # 转义单引号
            # 获取规则的 filter，这个filter决定了规则的具体执行条件，是一个列表
            filterData = ruleAct.get('filter', [])

            # ============ 生成 挑选 filte 和 relate 的 SQL 语句 ============
            # 创建 sqlList 的初始部分：将数据灌入 vlt_tmp_a(va) 表，增加并行处理速度
            sql0 = f"""
                -- 将触发器数据灌入 vlt_tmp_a 表
                INSERT INTO vlt_tmp_a (
                    d0_rowid, setl_id, psn_no, hsp_abbr, j_year, j_isin, item_j_code, item_j_name, 
                    item_time, item_day, item_hour, item_dept, apply_dept, q, p, c, b
                )
                SELECT /*+ INDEX(d IDX_FEE_HSP_ABBR_ITEM_TIME_ITEM_J_CODE_J_B) */
                    d0_rowid, setl_id, psn_no, hsp_abbr, j_year, j_isin, item_j_code, item_j_name, 
                    item_time, item_day, item_hour, item_dept, apply_dept, q, p, c, b
                FROM d 
                WHERE 1=1
            """


            # 创建 SQL 语句的初始部分
            # sql = "SELECT\n\td0_rowid, item_time_seq, setl_id, psn_no, hsp_abbr, j_year, j_isin, \n\titem_j_code, item_j_name, item_time, item_day, q, p, c, b,"
            # sql += f"\n\t'{ruleId}' AS rule_id, '{ruleActId}' AS rule_act_id, '{ruleActMsg}' AS rule_act_msg\n"
            # sql += "FROM d WHERE 1=1\n"
            # # 创建用于寻找关联信息的 sqlRelatedList
            # sqlRelatedList = []

            # 按照规则的开始日期、结束日期、filter_pre、trigger这些条件构造 SQL 语句
            sql0 = genIsValid(ruleDatum, ruleAct, filter_pre, hsp_abbr, sql0)
            sql0 = genDate(ruleDatum, ruleAct, filter_pre, hsp_abbr, sql0)
            sql0 = genHspLv(ruleDatum, ruleAct, filter_pre, hsp_abbr, sql0)
            sql0 = genInOrOut(ruleDatum, ruleAct, filter_pre, hsp_abbr, sql0)
            sql0 = genSelfpaid(ruleDatum, ruleAct, filter_pre, hsp_abbr, sql0)
            sql0 = genTrigger(ruleDatum, ruleAct, filter_pre, hsp_abbr, sql0)

            sqlList = [sql0]

            # 遍历filter列表，每一个元素 filterDatum 我们称为一个“算子”，代表一种筛选条件
            for filterDatum in filterData:
                if ruleId == 'I195':
                    # print('DEBUG')
                    pass

                # 获得field，这个field决定了当前算子的筛选方式
                field = filterDatum.get('field', {})

                # 针对 item_usage
                if field == 'item_usage':
                    sqlList += genItemUsage(ruleDatum, ruleAct, filterDatum, hsp_abbr)
                
                # 针对 dx
                elif field == 'dx':
                    sqlList += genDx(ruleDatum, ruleAct, filterDatum, hsp_abbr)

                # 针对 item
                elif field == 'item':
                    sqlList += genItem(ruleDatum, ruleAct, filterDatum, hsp_abbr)
                
                # 针对 age
                elif field == 'age':
                    sqlList += genAge(ruleDatum, ruleAct, filterDatum, hsp_abbr)
                
                # 针对 hsp
                elif field == 'hsp':
                    sqlList += genHsp(ruleDatum, ruleAct, filterDatum, hsp_abbr)
                
                # 针对 dayold
                elif field == 'dayold':
                    sqlList += genDayOld(ruleDatum, ruleAct, filterDatum, hsp_abbr)
                
                # 针对 gender
                elif field == 'gender':
                    sqlList += genGender(ruleDatum, ruleAct, filterDatum, hsp_abbr)
                
                # 针对 period
                elif field == 'period':
                    sqlList += genPeriod(ruleDatum, ruleAct, filterDatum, hsp_abbr)
                
                # 针对 med_type
                elif field == 'med_type':
                    sqlList += genMedType(ruleDatum, ruleAct, filterDatum, hsp_abbr)
                
                # 针对 item_num
                elif field == 'item_num':
                    sqlList += genItemNum(ruleDatum, ruleAct, filterDatum, hsp_abbr)
                
                # 针对 dept
                elif field == 'dept':
                    # 这里假设有一个处理 dept 的函数
                    sqlList += genDept(ruleDatum, ruleAct, filterDatum, hsp_abbr)
                
                # 针对 item_num_ratio
                elif field == 'item_num_ratio':
                    # 这里假设有一个处理 item_num_ratio 的函数
                    sqlList += genItemNumRatio(ruleDatum, ruleAct, filterDatum, hsp_abbr)
                
                # 针对 item_used_day
                elif field == 'item_used_day':
                    # 这里假设有一个处理 item_used_day 的函数
                    sqlList += genItemUsedDay(ruleDatum, ruleAct, filterDatum, hsp_abbr)
                
                # 针对 item_time
                elif field == 'item_time':
                    # 这里假设有一个处理 item_time 的函数
                    sqlList += genItemTime(ruleDatum, ruleAct, filterDatum, hsp_abbr)
                
                # 针对 item_first_time
                elif field == 'item_first_time':
                    # 这里假设有一个处理 item_first_time 的函数
                    sqlList += genItemFirstTime(ruleDatum, ruleAct, filterDatum, hsp_abbr)
                
                # 针对特殊规则 A27
                elif field == 'A27':
                    sqlList += genA27(ruleDatum, ruleAct, filterDatum, hsp_abbr)
                    
                # 针对特殊规则 A49
                elif field == 'A49':
                    sqlList += genA49(ruleDatum, ruleAct, filterDatum, hsp_abbr)

                # 针对特殊规则 A513
                elif field == 'A513':
                    sqlList += genA513(ruleDatum, ruleAct, filterDatum, hsp_abbr)
                
                # 针对特殊规则 A542
                elif field == 'A542':
                    sqlList += genA542(ruleDatum, ruleAct, filterDatum, hsp_abbr)
                    
                # 针对特殊规则 A8
                elif field == 'A8':
                    sqlList += genA8(ruleDatum, ruleAct, filterDatum, hsp_abbr)
                    
                # 针对特殊规则 B24
                elif field == 'B24':
                    sqlList += genB24(ruleDatum, ruleAct, filterDatum, hsp_abbr)
                    
                # 针对特殊规则 E79
                elif field == 'E79':
                    sqlList += genE79(ruleDatum, ruleAct, filterDatum, hsp_abbr)
                    
                # 针对特殊规则 I107
                elif field == 'I107':
                    sqlList += genI107(ruleDatum, ruleAct, filterDatum, hsp_abbr)
                    
                # 针对特殊规则 I137
                elif field == 'I137':
                    sqlList += genI137(ruleDatum, ruleAct, filterDatum, hsp_abbr)
                    
                # 针对特殊规则 I49
                elif field == 'I49':
                    sqlList += genI49(ruleDatum, ruleAct, filterDatum, hsp_abbr)
                    
                # 针对特殊规则 M179
                elif field == 'M179':
                    sqlList += genM179(ruleDatum, ruleAct, filterDatum, hsp_abbr)
                    
                # 针对特殊规则 M27
                elif field == 'M27':
                    sqlList += genM27(ruleDatum, ruleAct, filterDatum, hsp_abbr)
                    
                # 针对特殊规则 C21
                elif field == 'C21':
                    sqlList += genC21(ruleDatum, ruleAct, filterDatum, hsp_abbr)
                    


            # ============ 生成 计价 pricing SQL 语句 ============
            if ruleId == 'I128':
                # DEBUG
                pass
            sqlList += genViolates(ruleDatum, ruleAct, hsp_abbr)
            
            # 将 vlt_tmp_a 中的数据插入到 vlt 表中
            sqlList.append(
                f"""
                    -- 将 vlt_tmp_a 中的数据插入到 vlt 表中
                    INSERT INTO vlt (
                        d0_rowid, 
                        setl_id, psn_no, hsp_abbr, j_year, j_isin, 
                        item_j_code, item_j_name, item_time, item_day, q, p, c, b,
                        item_j_code2, item_j_name2, item_code2, item_name2, item_med_code2, item_hsp_code2, item_hsp_name2, 
                        item_time2, item_dept2, apply_dept2, p2, q2, c2, b2,
                        vlt_q, vlt_p, vlt_c, vlt_b,
                        rule_id, rule_act_id, rule_act_msg
                    )
                    SELECT 
                        d0_rowid, 
                        setl_id, psn_no, hsp_abbr, j_year, j_isin, 
                        item_j_code, item_j_name, item_time, item_day, q, p, c, b,
                        item_j_code2, item_j_name2, item_code2, item_name2, item_med_code2, item_hsp_code2, item_hsp_name2, 
                        item_time2, item_dept2, apply_dept2, p2, q2, c2, b2,
                        vlt_q, vlt_p, vlt_c, vlt_b,
                        '{ruleId}' AS rule_id, '{ruleActId}' AS rule_act_id, '{ruleActMsg}' AS rule_act_msg
                    FROM vlt_tmp_a va
                """
            )
            
            # 对 sqlList 中的每个 SQL 语句进行缩进处理
            sqlList = [textwrap.dedent(sql).strip() for sql in sqlList]
            
            # 保存当前的 SQL 语句到ruleAct中
            ruleAct['sqlList'] = sqlList

    # 将ruleData写入到ruleData_generated_{hsp_abbr}.json文件
    with open(f'ruleData_generated_{hsp_abbr}.json', 'w', encoding='utf-8') as f:
        json.dump(ruleData, f, ensure_ascii=False, indent=4)
    
    # 将当前这个 hsp_abbr 的 ruleData 保存到 ruleData_generated 表中
    print(f"Saving rules for {hsp_abbr} into rule_apply table...")
    # 将 ruleData 转换为扁平化的表格
    ruleDataTupleFlat = flatten_json(ruleData)
    ruleDataFlat = [dict(zip(['rule_id', 'key', 'type', 'value'], item)) for item in ruleDataTupleFlat]
    # 将 ruleData 当中属于 sqlList 里面的部分，不放在 value 中，而是放在 sql 中，额外保存为 ruleSqlFlat 中
    ruleSqlFlat = []
    for row in ruleDataFlat:
        if 'sqlList' in row['key']:
            row['sql'] = row['value']
            row['value'] = None
            ruleSqlFlat.append(row)
    # 将 ruleSqlFlat 中的每一行插入到 rule_apply 表中
    with engine.connect() as conn:
        for row in bar(ruleSqlFlat):
            conn.execute(text("""
                INSERT INTO rule_apply (hsp_abbr, rule_id, key, type, value, sql)
                VALUES (:hsp_abbr, :rule_id, :key, :type, :value, :sql)
            """), {
                'hsp_abbr': hsp_abbr,
                'rule_id': row['rule_id'],
                'key': row['key'],
                'type': row['type'],
                'value': row['value'] if row['value'] is not None else None,
                'sql': row['sql'] if 'sql' in row else None
            })
        conn.commit()
    
    print(f"Generated ruleData_generated_{hsp_abbr}.json successfully.")

print('============ 生成规则 SQL 语句完成 ============')